# -*- coding:utf-8 -*-
import os.path

import pandas as pd

from infrastructure.utils.dbUtil import SqliteUtil, SqliteConfig
from config.baseConfiguration import Sqlite3Config, ReportConfig
from infrastructure.utils.log import logger

config = SqliteConfig(dbPath=Sqlite3Config.dbPath)
filename = '市场投机分析.xlsx'
filename = os.path.join(ReportConfig.reportPath, filename)


def boardsView():
    db = SqliteUtil.get_instance(dbConfig=config)
    sql = f"""
        select b.dti as '交易日期'
                ,i.name as 股票名称, i.code as 股票编码
                ,case when b.type = 1 then '涨停' else '跌停' end as 封板类型
                ,b.count as 连扳数
                ,b.jtjb as 几天几板
                ,round(b.rise, 2) as 涨幅
                ,b.price as 现价
                ,b.first_time as 首次封板时间
                ,b.last_time as 最终封板时间
                ,b.open_count as 开板次数
                ,round(b.board_turnover/100000000, 2) as '封单额(亿)'
                ,round(b.market_capitalization/100000000, 2) as '流通市值(亿)'
                ,round(b.cje/100000000, 2) as '成交额(亿)'
                ,b.fcb as 封成比
                ,round(b.turnoverr, 2) as 换手
                ,b.board_state as 封板状态
                ,round(m.zf_open, 2) as 次日开盘涨幅
                ,round(m.zf_high, 2) as 次日最高涨幅
                ,round(m.zf_low, 2) as 次日最低涨幅
                ,round(m.zf_close, 2) as 次日收盘涨幅
                ,(select group_concat(r, '+')
                    from (
                        select '['|| bl.group_id||']'||gi.name as r
                        from stock_board_logic bl left join group_info gi on bl.group_id=gi.id
                        where bl.dti=b.dti and bl.code=b.code and bl.type=0
                    )) as 所属板块
                ,(select group_concat(r, '\n')
                    from (
                        select bl.info as r
                        from stock_board_logic bl 
                        where bl.dti=b.dti and bl.code=b.code and bl.type=1
                    )) as 涨停逻辑
        from stock_board b 
        left join stock_move m on m.code=b.code and m.dti=b.tdti
        left join stock_info i on b.code = i.code
        where 1=1
        --     and b.dti in ()
        --     and b.code = ''
        order by b.dti desc, b.first_time asc
        """
    dtsql = "select dti from market_sentiment order by dti desc limit 7"
    dtr = db.get_all(dtsql)
    if dtr is not None and len(dtr) > 0:
        dts = [str(dt[0]) for dt in dtr]
        sql = sql.replace("1=1", f"b.dti in ({','.join(dts)})")

    boards = db.get_all(sql)
    columns = list(zip(*db.cur.description))[0]
    if len(boards) == 0:
        return
    else:
        boards = [list(g) for g in boards]

    df = pd.DataFrame(boards, columns=columns)
    if os.path.exists(filename):
        with pd.ExcelWriter(filename, mode='a', if_sheet_exists='replace') as writer:
            df.to_excel(writer, sheet_name="封板分析", index=False)
    else:
        with pd.ExcelWriter(filename, mode='w') as writer:
            df.to_excel(writer, sheet_name="封板分析", index=False)


def spaceBoardView():
    db = SqliteUtil.get_instance(dbConfig=config)
    sql = f"""
        select dti as 日期, group_concat(rc, ', ') as 代码, group_concat(r, ', ') as '名称(开, 最高, 最低, 收)'
        from (
            select b.dti
                , b.count || '.' || i.code rc
                , case when b.price is not null and m.zf_open is not null then b.count || '.' || i.name || '(' || round(m.zf_open, 2) || ',' || round(m.zf_high, 2) || ',' || round(m.zf_low, 2) || ',' || round(m.zf_close, 2) || ')' 
                    else b.count || '.' || i.name end as r
                , b.count
            from stock_board b
                left join stock_move m on m.code=b.code and m.dti=b.tdti
                   left join stock_info i on b.code = i.code
            where b.type = 1 and b.count >= 3
            order by count desc
        ) group by dti
        order by dti desc
        """

    boards = db.get_all(sql)
    columns = list(zip(*db.cur.description))[0]
    if len(boards) == 0:
        return
    else:
        boards = [list(g) for g in boards]

    df = pd.DataFrame(boards, columns=columns)
    sheet_name = "空间分析"
    if os.path.exists(filename):
        with pd.ExcelWriter(filename, mode='a', if_sheet_exists='replace') as writer:
            df.to_excel(writer, sheet_name=sheet_name, index=False)
    else:
        with pd.ExcelWriter(filename, mode='w') as writer:
            df.to_excel(writer, sheet_name=sheet_name, index=False)


def sentimentView():
    db = SqliteUtil.get_instance(dbConfig=config)
    sql = """
        select
            ms.dti as '交易日期'
            ,ms.hps as 红盘
            ,ms.lps as 绿盘
            ,ms.hlzs as 总数
            ,round(ms.hps * 100.00/ms.hlzs, 2) as 红盘率
            ,ms.shzf as 上证涨幅
            ,ms.zcje as '总成交额(亿)'
            ,ms.zts as 涨停数
            ,ms.dts as 跌停数
            ,ms.zbs as 炸板数
            ,ms.lbs as 连扳数
            ,ms.lbs2 as '2连数'
            ,ms.lbs3 as '3连数'
            ,ms.lbs4p as '3+连扳'
            ,ms.kjb as 空间版
            ,ms.fbl as 封板率
            ,ms.lbbz as '连扳比值'
            ,ms.bl2j3 as '2进3'
            ,ms.bldjd as 高进高
            ,case when ms.tjqx = 0 then '弱' else '强' end as 投机情绪
            ,ms.remark as 备注
        from market_sentiment ms
        order by ms.dti desc
        """
    sentiments = db.get_all(sql)
    columns = list(zip(*db.cur.description))[0]
    if len(sentiments) == 0:
        return
    else:
        sentiments = [list(sentiment) for sentiment in sentiments]

    df = pd.DataFrame(sentiments, columns=columns)
    if os.path.exists(filename):
        with pd.ExcelWriter(filename, mode='a', if_sheet_exists='replace') as writer:
            df.to_excel(writer, sheet_name="市场情绪", index=False)
    else:
        with pd.ExcelWriter(filename, mode='w') as writer:
            df.to_excel(writer, sheet_name="市场情绪", index=False)


# 动能势能
def dnsnView():
    db = SqliteUtil.get_instance(dbConfig=config)
    sql = """
        select
            ms.dti as '交易日期'
            ,ms.hpbl as '红盘比(>=60%)'
            ,ms.zts as '涨停数(>=40)'
            ,ms.lbs as '连扳数(>=10)'
            ,ms.d5s as '大跌数(<100)'
            ,round(ms.d5s/ms.zts, 2) as '亏钱效应(2)'
            ,ms.sn as '势能'
            ,ms.zsbzbl as '首板涨比(>=60%)'
            ,ms.zsbddbl as '首板大面比(<30%)'
            ,ms.zlbzbl as '连扳涨比(>=60%)'
            ,ms.zlbddbl as '连扳大面比(<30%)'
            ,ms.lbbz as '连扳比值0.8'
            ,ms.bzlbdbl as '昨连未板跌比(<50%)'
            ,ms.dn as '动能'
            ,ms.shzf as 上证涨幅
            ,ms.shcje as '上海成交额(亿)'
            ,ms.szcje as '深圳成交额(亿)'
            ,ms.cycje as '创业成交额(亿)'
            ,ms.kccje as '科创成交额(亿)'
            ,ms.bjcje as '北京成交额(亿)'
            ,ms.zcje as '总成交额(亿)'
            ,round(ms.ztcje/100000000, 2) as '涨停成交额(亿)'
            ,round(ms.lbcje/100000000, 2) as '连扳成交额(亿)'
            ,round(ms.lb2cje/100000000, 2) as '2连成交额(亿)'
            ,round(ms.lb3cje/100000000, 2) as '3连成交额(亿)'
            ,round(ms.lb4pcje/100000000, 2) as '3+连成交额(亿)'
            ,round(ms.dtcje/100000000, 2) as '跌停成交额(亿)'
            ,ms.remark as 备注
        from market_sentiment ms
        order by ms.dti desc
        """
    sentiments = db.get_all(sql)
    columns = list(zip(*db.cur.description))[0]
    if len(sentiments) == 0:
        return
    else:
        sentiments = [list(sentiment) for sentiment in sentiments]
    df = pd.DataFrame(sentiments, columns=columns)
    sheet_name = "动能势能"
    if os.path.exists(filename):
        with pd.ExcelWriter(filename, mode='a', if_sheet_exists='replace') as writer:
            df.to_excel(writer, sheet_name=sheet_name, index=False)
    else:
        with pd.ExcelWriter(filename, mode='w') as writer:
            df.to_excel(writer, sheet_name=sheet_name, index=False)


def yestodayUpLimitDisplayView():
    db = SqliteUtil.get_instance(dbConfig=config)
    sql = """
        select
            ms.dti as '交易日期'
            ,ms.zfstztbx as '昨涨停!st今表现'
            ,ms.zsbfstbx as '昨首!st今表现'
            ,ms.zlbfstbx as '昨连!st今表现'
            ,ms.z2lbfstbx as '昨2连!st今表现'
            ,ms.z3lbfstbx as '昨3连!st今表现'
            ,ms.z4plbfstbx as '昨3+连!st今表现'
            ,ms.zlbzs as 昨连今涨数
            ,ms.zlbds as 昨连今跌数
            ,ms.zlbdzs as 昨连今大涨数
            ,ms.zlbdds as 昨连今大跌数
            ,ms.zlbzbl as 昨连今涨比
            ,ms.zlbdbl as 昨连今跌比
            ,ms.zlbdzbl as 昨连今大涨比
            ,ms.zlbddbl as 昨连今大跌比
            ,ms.z2lbzs as 昨2连今涨数
            ,ms.z2lbds as 昨2连今跌数
            ,ms.z2lbzbl as 昨2连今涨比
            ,ms.z2lbdbl as 昨2连今跌比
            ,ms.z2lbdzs as 昨2连今大涨数
            ,ms.z2lbdds as 昨2连今大跌数
            ,ms.z2lbdzbl as 昨2连今大涨比
            ,ms.z2lbddbl as 昨2连今大跌比
            ,ms.z3lbzs as 昨3连今涨数
            ,ms.z3lbds as 昨3连今跌数
            ,ms.z3lbzbl as 昨3连今涨比
            ,ms.z3lbdbl as 昨3连今跌比
            ,ms.z3lbdzs as 昨3连今大涨数
            ,ms.z3lbdds as 昨3连今大跌数
            ,ms.z3lbdzbl as 昨3连今大涨比
            ,ms.z3lbddbl as 昨3连今大跌比
            ,ms.z4plbzs as '昨3+连今涨数'
            ,ms.z4plbds as '昨3+连今跌数'
            ,ms.z4plbzbl as '昨3+连今涨比'
            ,ms.z4plbdbl as '昨3+连今跌比'
            ,ms.z4plbdzs as '昨3+连今大涨数'
            ,ms.z4plbdds as '昨3+连今大跌数'
            ,ms.z4plbdzbl as '昨3+连今大涨比'
            ,ms.z4plbddbl as '昨3+连今大跌比'
        from market_sentiment ms
        order by ms.dti desc
        """
    sentiments = db.get_all(sql)
    columns = list(zip(*db.cur.description))[0]
    if len(sentiments) == 0:
        return
    else:
        sentiments = [list(sentiment) for sentiment in sentiments]

    df = pd.DataFrame(sentiments, columns=columns)
    sheet_name = '昨日涨停分析'
    if os.path.exists(filename):
        with pd.ExcelWriter(filename, mode='a', if_sheet_exists='replace') as writer:
            df.to_excel(writer, sheet_name=sheet_name, index=False)
    else:
        with pd.ExcelWriter(filename, mode='w') as writer:
            df.to_excel(writer, sheet_name=sheet_name, index=False)


def planView():
    db = SqliteUtil.get_instance(dbConfig=config)
    sql = f"""
        select 
            i.name as 名称
            ,p.code as 编码
            ,p.dtp as 计划日期
            ,p.dta as 计划执行日期
            ,case when p.type = 0 then '卖出' else '买入' end as 计划类型
            ,p.tactics as 策略
            ,p.price as 价格
            ,p.vol as 计划量
            ,p.position as '仓位(%)'
            ,p.plan as 内容
            ,case when p.status = 0 then '未开始' when p.status = 1 then '已执行' else '未执行' end as 状态
            ,p.desc as 描述
        from plan p left join stock_info i on p.code = i.code
        where 1=1
        order by id desc 
    """
    dtsql = "select distinct dta from plan order by dta desc limit 3"
    dtr = db.get_all(dtsql)
    if dtr is not None and len(dtr) > 0:
        dts = [str(dt[0]) for dt in dtr]
        sql = sql.replace("1=1", f"p.dta in ({','.join(dts)})")

    results = db.get_all(sql)
    columns = list(zip(*db.cur.description))[0]
    if len(results) == 0:
        return
    else:
        rows = [list(g) for g in results]

    df = pd.DataFrame(rows, columns=columns)
    if os.path.exists(filename):
        with pd.ExcelWriter(filename, mode='a', if_sheet_exists='replace') as writer:
            df.to_excel(writer, sheet_name="执行计划", index=False)
    else:
        with pd.ExcelWriter(filename, mode='w') as writer:
            df.to_excel(writer, sheet_name="执行计划", index=False)


def actionView():
    db = SqliteUtil.get_instance(dbConfig=config)
    sql = f"""
        select 
            i.name as 名称
            ,a.code as 编码
            ,a.dt as '执行日期'
            ,a.ti as '执行时间'
            ,case when a.type = 0 then '卖出' else '买入' end as 类型
            ,a.tactics as 策略
            ,a.price as 价格
            ,a.vol as 量
            ,a.position as '仓位(%)'
            ,a.action as 内容
        from action a left join stock_info i on a.code = i.code
        where 1=1
        order by dt desc 
    """
    dtsql = "select dti from market_sentiment order by dti desc limit 3"
    dtr = db.get_all(dtsql)
    if dtr is not None and len(dtr) > 0:
        dts = [str(dt[0]) for dt in dtr]
        sql = sql.replace("1=1", f"a.dt in ({','.join(dts)})")

    results = db.get_all(sql)
    columns = list(zip(*db.cur.description))[0]
    if len(results) == 0:
        return
    else:
        rows = [list(g) for g in results]

    df = pd.DataFrame(rows, columns=columns)
    if os.path.exists(filename):
        with pd.ExcelWriter(filename, mode='a', if_sheet_exists='replace') as writer:
            df.to_excel(writer, sheet_name="执行", index=False)
    else:
        with pd.ExcelWriter(filename, mode='w') as writer:
            df.to_excel(writer, sheet_name="执行", index=False)


def stockTip():
    db = SqliteUtil.get_instance(dbConfig=config)
    sql = """
        select
            t.dti '交易日期'
            ,i.name 名称
            ,t.code 编码
            ,t.tip 提示
            ,t.desc 描述
        from stock_tips t
            left join stock_info i on t.code = i.code
        where t.dti in (select distinct dti from stock_tips order by dti desc limit 3) 
            and t.type == 0
        order by t.dti desc
        """
    datas = db.get_all(sql)
    if datas is not None and len(datas) > 0:
        columns = list(zip(*db.cur.description))[0]
        df = pd.DataFrame(datas, columns=columns)
        if os.path.exists(filename):
            with pd.ExcelWriter(filename, mode='a', if_sheet_exists='replace') as writer:
                df.to_excel(writer, sheet_name="个股提示", index=False)
        else:
            with pd.ExcelWriter(filename, mode='w') as writer:
                df.to_excel(writer, sheet_name="个股提示", index=False)


def indexTip():
    db = SqliteUtil.get_instance(dbConfig=config)
    # SQL查询语句
    sql = """
        select
            t.dti '交易日期'
            ,i.name 名称
            ,t.code 编码
            ,t.tip 提示
            ,t.desc 描述
        from stock_tips t
            left join stock_info i on t.code = i.code
        where t.dti in (select distinct dti from stock_tips order by dti desc limit 10) 
            and t.type == 1
        order by t.dti desc
        """
    # 执行SQL查询，获取数据
    datas = db.get_all(sql)
    if datas is not None and len(datas) > 0:
        # 获取数据列的标题
        columns = list(zip(*db.cur.description))[0]
        # 将数据转换为DataFrame
        df = pd.DataFrame(datas, columns=columns)

        # 判断文件是否存在
        if os.path.exists(filename):
            # 如果文件存在，则以追加模式打开Excel文件，并替换同名sheet
            with pd.ExcelWriter(filename, mode='a', if_sheet_exists='replace') as writer:
                df.to_excel(writer, sheet_name="指数提示", index=False)
        else:
            # 如果文件不存在，则以写入模式打开Excel文件
            with pd.ExcelWriter(filename, mode='w') as writer:
                df.to_excel(writer, sheet_name="指数提示", index=False)


def sentimentViewMain():
    logger.info("开始生成市场总体投机报告")
    sentimentView()
    dnsnView()
    yestodayUpLimitDisplayView()
    spaceBoardView()

    boardsView()
    planView()
    actionView()

    stockTip()
    indexTip()
    logger.info("成功生成市场总体投机报告")


if __name__ == "__main__":
    sentimentViewMain()
